/***********************************************************************************************************************************
 *    Name:	Job_DatabaseTLogBackup.sql
 *  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
 * Summary:	Schedule transaction log backups.
 */
USE msdb;
GO

-- [Backup Transaction Log] ========================================================================================================
DECLARE
	@JobName		NVARCHAR(128),
	@OutputFileName	NVARCHAR(255),
	@jobID			BINARY(16);

SET @JobName= N'• Database TLog Backup';
SET @OutputFileName= master.DBAdmin.GetInstancePath(N'WorkingDirectory') + + N'BackupTLogs.txt';
SELECT @jobID= job_id FROM msdb.dbo.sysjobs WHERE name = @JobName;
IF @jobID IS Null BEGIN
	EXECUTE msdb.dbo.sp_add_job
		@job_name			= @JobName,
		@category_name		= N'DBAdmin',
		@owner_login_name	= N'sa',
		@job_id				= @jobID OUTPUT;
	EXECUTE msdb.dbo.sp_add_jobserver @job_id= @jobID, @server_name= N'(LOCAL)';
END ELSE EXECUTE msdb.dbo.sp_delete_jobstep @job_id= @jobID, @step_id= 0;

EXECUTE msdb.dbo.sp_update_job
	@job_id						= @jobID,
--	@enabled					= 1,
	@notify_level_page			= 0,	-- Never
	@notify_level_netsend		= 0,	-- Never
	@notify_level_eventlog		= 0,	-- Never
	@notify_level_email			= 2,	-- On Failure
	@notify_email_operator_name	= N'(DB)A-Team';

EXECUTE msdb.dbo.sp_add_jobstep
	@job_id				= @jobID,
	@step_name			= N'Backup Transaction Log',
	@command			= N'EXECUTE DBAdmin.BackupTLOGs;',
	@output_file_name	= @OutputFileName;

BEGIN TRY;
  EXECUTE msdb.dbo.sp_attach_schedule @job_name= @JobName, @schedule_name= @JobName;
END TRY BEGIN CATCH END CATCH;
GO